﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'P_DeleteUserMenu')
	BEGIN
		DROP  Procedure  [esf_sso].P_DeleteUserMenu
	END

GO


CREATE Procedure [esf_sso].P_DeleteUserMenu
	@pID	INT
AS

	
	WITH TreeBasRef (ParentID, ID, Code, [Name])
	AS
	(
		-- First Level
		SELECT 
			NULL	ParentID
			,VBR.ID
			,VBR.Code
			,VBR.Name
		FROM 
			esf_sso.VBasicReference VBR
		WHERE 
			VBR.ID = @pID

		UNION ALL
		-- Recursive member definition
		SELECT 
			TBRF.ID	ParentID
			,VBR.ID
			,VBR.Code
			,VBR.Name
		FROM 
			esf_sso.VBasicReference VBR
		INNER JOIN
			esf_sso.TreeBasicReference TBR ON TBR.BasicReferenceID = VBR.ID
		INNER JOIN
			TreeBasRef TBRF ON TBRF.ID = TBR.ParentBasicReferenceID
	)

	SELECT ID INTO #TreeBasRef FROM TreeBasRef ORDER BY ID DESC

	DELETE FROM esf_sso.TreeBasicReference 
	WHERE 
		BasicReferenceID IN (SELECT ID FROM #TreeBasRef)
		OR ParentBasicReferenceID IN (SELECT ID FROM #TreeBasRef)
	
	DELETE FROM esf_sso.BasicReference WHERE ID IN (SELECT ID FROM #TreeBasRef)

	DROP TABLE #TreeBasRef

GO
